/*
  Tên:Nguyễn Văn Huy
  MSSV:11374891
  Lớp:DHTH7DLT
  Ngày hoàn thành:28/04/2012
  */
#include "frbaocaocongno.h"
#include "ui_frbaocaocongno.h"
#include "mysqlconnection.h"
#include "qmessagebox.h"
frbaocaocongno::frbaocaocongno(QWidget *parent) :
    QDialog(parent),
    ui(new Ui::frbaocaocongno)
{
    ui->setupUi(this);
    hienthibangcongno();
    ui->txtdate->setDisplayFormat("dd-MMM-yyyy");
    connect(ui->btxembaocao,SIGNAL(clicked()),this,SLOT(chonthanghienthi()));
    ui->rdtongtien->setChecked(true);
    ui->txtdate->setDate(QDate::currentDate());
}

frbaocaocongno::~frbaocaocongno()
{
    delete ui;

}
void frbaocaocongno::hienthibangcongno()
{
    MySqlConnection *DB=new MySqlConnection();
    DB->Open();
    QSqlQueryModel *model = new QSqlQueryModel();
    model->setQuery("select k.MaKH,k.TenKH, k.Nodau,sum(c.nocuoi) as phatsinh,(sum(c.nocuoi) + k.Nodau) as nocuoi from QLBSach.KhachHang k join QLBSach.Congno c where c.MaKH=k.MaKH  group by c.MaKH  ", DB->DB);
    ui->tbcongno->setModel(model);
    ui->tbcongno->show();
    ui->tbcongno->setColumnWidth(0,30);
    ui->tbcongno->setColumnWidth(1,250);
    ui->tbcongno->setColumnWidth(2,100);
    ui->tbcongno->setColumnWidth(3,100);
    ui->tbcongno->setColumnWidth(4,100);
    DB->Close();
}

void frbaocaocongno::chonthanghienthi()
{
    MySqlConnection *DB=new MySqlConnection();
    DB->Open();
    ///QSqlQuery queryshow(DB->DB);
    QSqlQueryModel *model = new QSqlQueryModel();
    QString sql;
    QStringList headerlist ;
    int monthnow=ui->txtdate->date().month();
    int yearnow=ui->txtdate->date().year();
    if(ui->rdchitiet->isChecked())
    {
        model->setHeaderData(0, Qt::Horizontal,  QString::fromUtf8("Mã Khách hàng"));
        model->setHeaderData(1, Qt::Horizontal,  QString::fromUtf8("Tên khách hàng"));
        model->setHeaderData(2, Qt::Horizontal,  QString::fromUtf8("Ngày nợ"));
        model->setHeaderData(3, Qt::Horizontal,  QString::fromUtf8("Nợ đầu"));
        model->setHeaderData(4, Qt::Horizontal,  QString::fromUtf8("Phát sịnh"));
        model->setHeaderData(5, Qt::Horizontal,  QString::fromUtf8("Nợ cuối"));
       // headerlist << QString::fromUtf8("Mã Khách hàng") << QString::fromUtf8("Tên khách hàng")<< QString::fromUtf8("Ngày nợ")<<QString::fromUtf8("Nợ đầu")<<QString::fromUtf8("Phát sịnh")<<QString::fromUtf8("Nợ cuối");
        sql="select k.MaKH,k.TenKH,c.Ngayno, k.Nodau,sum(c.nocuoi) as phatsinh,(sum(c.nocuoi) + k.Nodau) as nocuoi from QLBSach.KhachHang k join QLBSach.Congno c where c.MaKH=k.MaKH and MONTH(c.Ngayno) ='" + QString::number(monthnow) + "' and YEAR(c.Ngayno)='" + QString::number(yearnow) + "'  group by c.idCongno";
        //queryshow.prepare("select k.MaKH,k.TenKH,c.Ngayno, k.Nodau,sum(c.nocuoi) as phatsinh,(sum(c.nocuoi) + k.Nodau) as nocuoi from QLBSach.KhachHang k join QLBSach.Congno c where c.MaKH=k.MaKH and MONTH(c.Ngayno) = ?  group by c.idCongno");
        //queryshow.bindValue(0,monthnow);
    }else{
        model->setHeaderData(0, Qt::Horizontal,  QString::fromUtf8("Mã Khách hàng"));
        model->setHeaderData(1, Qt::Horizontal,  QString::fromUtf8("Tên khách hàng"));
        //model->setHeaderData(2, Qt::Horizontal,  QString::fromUtf8("Ngày nợ"));
        model->setHeaderData(2, Qt::Horizontal,  QString::fromUtf8("Nợ đầu"));
        model->setHeaderData(3, Qt::Horizontal,  QString::fromUtf8("Phát sịnh"));
        model->setHeaderData(4, Qt::Horizontal,  QString::fromUtf8("Nợ cuối"));
        //headerlist << QString::fromUtf8("Mã Khách hàng") << QString::fromUtf8("Tên khách hàng")<<QString::fromUtf8("Nợ đầu")<<QString::fromUtf8("Phát sịnh")<<QString::fromUtf8("Nợ cuối");
        sql="select k.MaKH,k.TenKH, k.Nodau,sum(c.nocuoi) as phatsinh,(sum(c.nocuoi) + k.Nodau) as nocuoi from QLBSach.KhachHang k join QLBSach.Congno c where c.MaKH=k.MaKH and MONTH(c.Ngayno) = '" + QString::number(monthnow) + "' and YEAR(c.Ngayno)='" + QString::number(yearnow) + "' group by c.MaKH";
         //queryshow.prepare("select k.MaKH,k.TenKH, k.Nodau,sum(c.nocuoi) as phatsinh,(sum(c.nocuoi) + k.Nodau) as nocuoi from QLBSach.KhachHang k join QLBSach.Congno c where c.MaKH=k.MaKH and MONTH(c.Ngayno) = ? group by c.MaKH ");
         //queryshow.bindValue(0,monthnow);
    }

    model->setQuery(sql,DB->DB);
    while(model->canFetchMore( ))
            model->fetchMore( );
    //ui->tbcongno->setHorizontalHeader(headerlist);
    ui->tbcongno->verticalHeader()->hide();
    ui->tbcongno->resizeColumnsToContents();
    ui->tbcongno->setModel(model);
    ui->tbcongno->setColumnWidth(0,30);
    ui->tbcongno->setColumnWidth(1,250);
    ui->tbcongno->setColumnWidth(2,100);
    ui->tbcongno->setColumnWidth(3,100);
    ui->tbcongno->setColumnWidth(4,100);
    ui->tbcongno->setColumnWidth(5,100);
    ui->tbcongno->show();

}

void frbaocaocongno::thoat()
{
}
